package srdb.migration.dbhandler.sqlserver;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.TimeZone;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import oracle.jdbc.OracleResultSet;
import oracle.sql.BLOB;
import srdb.migration.MigrationToolkit;
import srdb.migration.Utility;
import srdb.migration.common.IMTKConnection;
import srdb.migration.common.MTKBridge;
import srdb.migration.common.MTKConstants;
import srdb.migration.i18n.ResourceHandler;
import srdb.migration.schema.metadata.Column;
import srdb.migration.schema.metadata.Table;

public class PGBridge extends MTKBridge {
	protected static final String BLOB_FILE_NAME = "mtk_blobdata";

	public PGBridge(IMTKConnection srcConn, IMTKConnection targetConn) {
		super(srcConn, targetConn);
	}

	public long setTableData(Table table, int batchSize,
			MTKConstants.DATABASE_TYPE sourceDBType,
			MTKConstants.DATABASE_TYPE targetDBType) throws Exception {
		long committedRowCount = 0L;
		boolean lastBatchFailure = false;
		Connection _edbConn = null;
		boolean defaultAutoCommitMode = false;
		PreparedStatement insertStmt = null;
		Statement sourceStmt = null;
		Statement destStmt = null;
		ResultSet rs = null;
		String insertStr = "";
		boolean isSerial = false;

		File f = null;
		FileOutputStream fos = null;
		FileInputStream fis = null;

		FileInputStream[] arrFis = new FileInputStream[table
				.getLargeObjectsColumnCount()];
		BufferedReader[] arrBr = new BufferedReader[table.getCLOBColumnCount()];
		int fisIndex = 0;
		int brIndex = 0;
		for (int i = 0; i < arrFis.length; i++) {
			arrFis[i] = null;
		}

		for (int i = 0; i < arrBr.length; i++) {
			arrBr[i] = null;
		}
		String fileNamePrefix = Utility.getValidFileNamePrefixFor(table
				.getParentSchema().getName(), table.getName());

		int j = 0;
		try {
			this.targetConn.getConnection().setAutoCommit(false);
			insertStr = "INSERT INTO " + table.getTargetSchemaQualifiedName();
			insertStr = insertStr + convertTableObjColumnList(table);
			insertStr = insertStr + " VALUES (";
			for (int i = 0; i < table.getColumns().size(); i++) {
				if ((table.getColumns().get(i).getDataTypeString()
						.equalsIgnoreCase("SERIAL"))
						|| (table.getColumns().get(i).getDataTypeString()
								.equalsIgnoreCase("BIGSERIAL"))) {
					insertStr = insertStr + "?";
					isSerial = true;
				} else if (table.getColumns().get(i).getDataType() == Column.DataType.GEOMETRIC) {
					if ((table.getColumns().get(i).getDataTypeString()
							.equalsIgnoreCase("POINT"))
							|| (table.getColumns().get(i).getDataTypeString()
									.equalsIgnoreCase("PATH"))) {
						insertStr = insertStr
								+ "geometry::STGeomFromText(?, 0)";
					} else if ((table.getColumns().get(i).getDataTypeString()
							.equalsIgnoreCase("LSEG"))
							|| (table.getColumns().get(i).getDataTypeString()
									.equalsIgnoreCase("BOX"))) {
						insertStr = insertStr
								+ "geometry::STLineFromText(?, 0)";
					} else if (table.getColumns().get(i).getDataTypeString()
							.equalsIgnoreCase("POLYGON"))
						insertStr = insertStr
								+ "geometry::STPolyFromText(?, 0)";
				} else {
					insertStr = insertStr + "?";
				}
				if (i < table.getColumns().size() - 1) {
					insertStr = insertStr + ", ";
				}
			}
			insertStr = insertStr + ")";
			insertStmt = this.targetConn.getConnection().prepareStatement(
					insertStr);

			destStmt = this.targetConn.getConnection().createStatement();
			destStmt.execute("TRUNCATE TABLE "
					+ table.getTargetSchemaQualifiedName());

			if (isSerial) {
				destStmt.execute("SET IDENTITY_INSERT "
						+ table.getTargetSchemaQualifiedName() + " ON;");
			}
			this.targetConn.commit();

			_edbConn = this.srcConn.getConnection();
			defaultAutoCommitMode = _edbConn.getAutoCommit();
			_edbConn.setAutoCommit(false);

			sourceStmt = this.srcConn.getConnection().createStatement();

			sourceStmt.setFetchSize(100);
			rs = sourceStmt.executeQuery(table.getSelectSQL());

			while (rs.next()) {
				for (int i = 0; i < table.getColumns().size(); i++) {
					Column column = table.getColumns().get(i);
					switch
					// (1.$SwitchMap$com$edb$schema$metadata$Column$DataType[table.getColumns().get(i).getDataType().ordinal()])
					(Column.DataType.values()[table.getColumns().get(i)
							.getDataType().ordinal()]) {
					// case 1:
					// case 2:
					// case 3:
					// case 4:
					// case 5:
					case VARCHAR:
						insertStmt.setString(i + 1, rs.getString(i + 1));
						break;
					case NUMERIC:
						if (rs.getObject(i + 1) != null) {
							insertStmt.setDouble(i + 1, rs.getDouble(i + 1));
						} else {
							insertStmt.setNull(i + 1, 6);
						}

						break;
					// case 8:
					// case 9:
					case FLOAT:
						Object objVal = rs.getObject(i + 1);

						if (column.isReal()) {
							if (objVal != null)
								insertStmt.setFloat(i + 1, rs.getFloat(i + 1));
							else
								insertStmt.setNull(i + 1, 6);
						} else if (column.isDouble()) {
							if (objVal != null) {
								if (this.srcConn.getConnection().getMetaData()
										.getDatabaseProductName()
										.equalsIgnoreCase("ENTERPRISEDB"))
									insertStmt.setDouble(i + 1,
											rs.getFloat(i + 1));
								else
									insertStmt.setDouble(i + 1,
											new Double(rs.getString(i + 1))
													.doubleValue());
							} else {
								insertStmt.setNull(i + 1, 8);
							}
						} else if (objVal != null)
							insertStmt.setBigDecimal(i + 1,
									rs.getBigDecimal(i + 1));
						else {
							insertStmt.setNull(i + 1, 2);
						}

						break;
					case BYTEA:
						if (column.isBlob()) {
							fisIndex = processBlob(rs, i, fileNamePrefix,
									fisIndex, arrFis, insertStmt);
						} else {
							byte[] b = rs.getBytes(i + 1);
							if (b != null) {
								this.lastMigratedDataSize += b.length;
								f = new File(Utility.getTempPath()
										+ "mtk_blobdata" + "_" + fileNamePrefix
										+ fisIndex);
								fos = new FileOutputStream(f);
								fos.write(b);
								fos.close();
								arrFis[fisIndex] = new FileInputStream(f);
								insertStmt.setBinaryStream(i + 1,
										arrFis[(fisIndex++)], (int) f.length());
							} else {
								insertStmt.setNull(i + 1, -2);
							}
						}
						break;
					case TIMESTAMP:
						insertStmt.setTimestamp(i + 1, rs.getTimestamp(i + 1));
						break;
					case DATE:
						if (column.getDataTypeString().equalsIgnoreCase(
								"DATETIMEOFFSET")) {
							DateFormat df = new SimpleDateFormat(
									"YYYY-MM-DDThh:mm:ss[.nnnnnnn]Z (UTC)");
							Timestamp tstamp = new Timestamp(0L);
							String tswtzVal = df.format(Long.valueOf(rs
									.getTimestamp(i + 1).getTime()));
							insertStmt
									.setTimestamp(i + 1,
											rs.getTimestamp(i + 1), Calendar
													.getInstance(TimeZone
															.getDefault()));
						} else {
							insertStmt.setTimestamp(i + 1,
									rs.getTimestamp(i + 1));
						}
						break;
					case TIME:
						insertStmt.setTime(i + 1, rs.getTime(i + 1));
						break;
					case NVARCHAR:
						insertStmt.setString(i + 1, rs.getString(i + 1));
						break;
					case TEXT:
						if (rs.getString(i + 1) == null)
							insertStmt.setString(i + 1, null);
						else {
							insertStmt.setString(i + 1, rs.getString(i + 1)
									.equalsIgnoreCase("t") ? "1" : "0");
						}
						break;
					case GEOMETRIC:
						if (table.getColumns().get(i).getDataTypeString()
								.equalsIgnoreCase("POINT")) {
							insertStmt.setString(i + 1,
									convertToSQLServerPointFormat(rs
											.getString(i + 1)));
						} else {
							if ((!table.getColumns().get(i).getDataTypeString()
									.equalsIgnoreCase("LSEG"))
									&& (!table.getColumns().get(i)
											.getDataTypeString()
											.equalsIgnoreCase("BOX"))
									&& (!table.getColumns().get(i)
											.getDataTypeString()
											.equalsIgnoreCase("POLYGON"))
									&& (!table.getColumns().get(i)
											.getDataTypeString()
											.equalsIgnoreCase("PATH"))) {
								continue;
							}
							insertStmt.setString(
									i + 1,
									convertToSQLServerLineFormat(
											rs.getString(i + 1), table
													.getColumns().get(i)
													.getDataTypeString()));
						}
						break;
					case INTERVAL:
						String intervalValue = rs.getString(i + 1);
						insertStmt.setString(i + 1, intervalValue);
						break;
					// case :
					// insertStmt.setString(i + 1, rs.getString(i + 1));
					// break;
					// case 20:
					// insertStmt.setString(i + 1, rs.getString(i + 1));
					// break;
					default:
						throw new Exception("Invalid DataType");
					}
				}
				insertStmt.addBatch();
				j++;

				if (j % batchSize != 0)
					continue;
				try {
					insertStmt.executeBatch();
					lastBatchFailure = false;
					Utility.getLogger().info(
							"["
									+ table.getName()
									+ "] "
									+ ResourceHandler
											.getValue("INFO_MIGRATED_ROWS",
													new Object[] { Integer
															.valueOf(j) }));
				} catch (Exception ex) {
					Utility.getLogger().warn(
							ex.getMessage() + ", Skipping Batch");
					Utility.getLogger().error("Stack Trace:", ex);
					MigrationToolkit.incrementFailureCount();
					table.incrementBatchFailureCount();
					lastBatchFailure = true;
				}
				insertStmt.clearBatch();
				this.targetConn.commit();

				for (int i = 0; i < arrFis.length; i++) {
					if (arrFis[i] != null) {
						arrFis[i].close();
						new File(Utility.getTempPath() + "mtk_blobdata" + "_"
								+ fileNamePrefix + i).delete();
					}
				}

				fisIndex = 0;

				if (!lastBatchFailure) {
					committedRowCount += batchSize;
				}

			}

			int[] lastBatchCmdCount = null;
			try {
				lastBatchCmdCount = insertStmt.executeBatch();
				lastBatchFailure = false;

				if (lastBatchCmdCount.length > 0) {
					committedRowCount += j % batchSize;
					Utility.getLogger()
							.info("["
									+ table.getName()
									+ "] "
									+ ResourceHandler
											.getValue(
													"INFO_MIGRATED_ROWS",
													new Object[] { Long
															.valueOf(committedRowCount) }));
				}
			} catch (Exception ex) {
				Utility.getLogger().warn(ex.getMessage() + ", Skipping Batch");
				Utility.getLogger().error("Stack Trace:", ex);
				MigrationToolkit.incrementFailureCount();
				table.incrementBatchFailureCount();
				lastBatchFailure = true;
			}
			insertStmt.clearBatch();
			this.targetConn.commit();

			for (int i = 0; i < arrFis.length; i++) {
				if (arrFis[i] != null) {
					arrFis[i].close();
					new File(Utility.getTempPath() + "mtk_blobdata" + "_"
							+ fileNamePrefix + i).delete();
				}
			}

			fisIndex = 0;

			if (isSerial) {
				destStmt.execute("SET IDENTITY_INSERT "
						+ table.getTargetSchemaQualifiedName() + " OFF;");
				isSerial = false;
			}

		} catch (SQLException SQLe) {
			this.targetConn.rollback();
			if (SQLe.getNextException() != null) {
				throw SQLe.getNextException();
			}
			throw SQLe;
		} catch (Exception ex) {
			this.targetConn.rollback();
			throw ex;
		} finally {
			if (_edbConn != null) {
				_edbConn.setAutoCommit(defaultAutoCommitMode);
			}

			this.targetConn.getConnection().setAutoCommit(true);

			if (insertStmt != null) {
				insertStmt.close();
			}

			if (destStmt != null) {
				destStmt.close();
			}

			if (rs != null) {
				rs.close();
			}

			if (sourceStmt != null) {
				sourceStmt.close();
			}
		}
		return committedRowCount;
	}

	public long setTableDataInSafeMode(Table table,
			MTKConstants.DATABASE_TYPE sourceDBType,
			MTKConstants.DATABASE_TYPE targetDBType) throws Exception {
		Statement insertStmt = null;
		Statement sourceStmt = null;
		Statement destStmt = null;
		ResultSet rs = null;
		StringBuffer insertColStr = new StringBuffer();
		StringBuffer insertStr = new StringBuffer();
		boolean isSerial = false;
		try {
			for (int i = 0; i < table.getColumns().size(); i++) {
				if ((!table.getColumns().get(i).getDataTypeString()
						.equalsIgnoreCase("INT IDENTITY"))
						&& (!table.getColumns().get(i).getDataTypeString()
								.equalsIgnoreCase("BIGINT IDENTITY")))
					continue;
				isSerial = true;
			}

			insertColStr.append("INSERT INTO ")
					.append(table.getTargetSchemaQualifiedName())
					.append(convertTableObjColumnList(table)).append(" ")
					.append(" VALUES (");

			insertStmt = this.targetConn.getConnection().createStatement();
			destStmt = this.targetConn.getConnection().createStatement();
			destStmt.execute("TRUNCATE TABLE "
					+ table.getTargetSchemaQualifiedName());

			if (isSerial) {
				destStmt.execute("SET IDENTITY_INSERT "
						+ table.getTargetSchemaQualifiedName() + " ON;");
			}

			sourceStmt = this.srcConn.getConnection().createStatement();
			rs = sourceStmt.executeQuery(table.getSelectSQL());

			int rowCount = 0;
			int i;
			while (rs.next()) {
				insertStr.setLength(0);
				insertStr.append(insertColStr);

				for (i = 0; i < table.getColumns().size(); i++) {
					if (i > 0) {
						insertStr.append(", ");
					}

					if (rs.getObject(i + 1) == null) {
						insertStr.append("null");
					} else {
						switch
						// (1.$SwitchMap$com$edb$schema$metadata$Column$DataType[table.getColumns().get(i).getDataType().ordinal()])
						(Column.DataType.values()[table.getColumns().get(i)
								.getDataType().ordinal()]) {
						// case 1:
						// case 2:
						// case 3:
						// case 4:
						case NVARCHAR:
							insertStr.append("'").append(rs.getString(i + 1))
									.append("'");
							// break;
							// case 7:
							// case 8:
							// case :
							// insertStr.append(rs.getBigDecimal(i + 1));
							// break;
						case DATE:
							insertStr.append("'").append(rs.getDate(i + 1))
									.append("'");
							break;
						case TIME:
							insertStr.append("'").append(rs.getTime(i + 1))
									.append("'");
							break;
						case TIMESTAMP:
							insertStr.append("'")
									.append(rs.getTimestamp(i + 1)).append("'");
							break;
						case OTHER:
							insertStr.append("null");
							break;
						case VARCHAR:
							insertStr.append("'").append(rs.getString(i + 1))
									.append("'");
							break;
						case BIT:
							insertStr.append(rs.getString(i + 1)
									.equalsIgnoreCase("t") ? "1" : "0");
							break;
						case GEOMETRIC:
							if (table.getColumns().get(i).getDataTypeString()
									.equalsIgnoreCase("POINT")) {
								insertStr
										.append("geometry::STGeomFromText('")
										.append(convertToSQLServerPointFormat(rs
												.getString(i + 1)))
										.append("',0)");
							} else if ((table.getColumns().get(i)
									.getDataTypeString()
									.equalsIgnoreCase("BOX"))
									|| (table.getColumns().get(i)
											.getDataTypeString()
											.equalsIgnoreCase("LSEG"))
									|| (table.getColumns().get(i)
											.getDataTypeString()
											.equalsIgnoreCase("PATH"))) {
								insertStr
										.append("geometry::STLineFromText('")
										.append(convertToSQLServerLineFormat(
												rs.getString(i + 1), table
														.getColumns().get(i)
														.getDataTypeString()))
										.append("',0)");
							} else {
								if (!table.getColumns().get(i)
										.getDataTypeString()
										.equalsIgnoreCase("POLYGON"))
									continue;
								insertStr
										.append("geometry::STPolyFromText('")
										.append(convertToSQLServerLineFormat(
												rs.getString(i + 1), table
														.getColumns().get(i)
														.getDataTypeString()))
										.append("',0)");
							}
							break;
						case TEXT:
							insertStr.append("'").append(rs.getString(i + 1))
									.append("'");
							break;
						case BITSTRING:
							insertStr.append("'").append(rs.getString(i + 1))
									.append("'");
							break;
						// case 5:
						// case 10:
						// case 19:
						default:
							throw new Exception("Invalid DataType");
						}
					}
				}
				insertStr.append(");");
				insertStmt.executeUpdate(insertStr.toString());

				rowCount++;
			}
			if (isSerial) {
				destStmt.execute("SET IDENTITY_INSERT "
						+ table.getTargetSchemaQualifiedName() + " OFF;");
			}

			// i = rowCount;
			return rowCount;
		} finally {
			int i;
			if (insertStmt != null) {
				insertStmt.close();
			}

			if (destStmt != null) {
				destStmt.close();
			}

			if (rs != null) {
				rs.close();
			}

			if (sourceStmt != null)
				sourceStmt.close();
		}
	}

	private String convertToSQLServerPointFormat(String pointValue) {
		String replacedValue = "POINT";
		replacedValue = replacedValue + pointValue.replaceAll(",", " ");
		return replacedValue;
	}

	private String convertToSQLServerLineFormat(String pointValue,
			String dataType) {
		String source = "";
		String geometryPattern = "LINESTRING";

		if ((dataType.equalsIgnoreCase("LSEG"))
				|| (dataType.equalsIgnoreCase("PATH"))) {
			source = pointValue.substring(1, pointValue.length() - 1);
			geometryPattern = "LINESTRING";
		} else {
			source = pointValue;
			if (dataType.equalsIgnoreCase("BOX"))
				geometryPattern = "LINESTRING";
			else if (dataType.equalsIgnoreCase("POLYGON")) {
				geometryPattern = "POLYGON";
			}
		}

		String find = "\\),\\(";
		String replace = ")(";
		Pattern pattern = Pattern.compile(find);
		Matcher matcher = pattern.matcher(source);
		String output = matcher.replaceAll(replace);
		output = output.replaceAll(",", " ");
		find = "\\)\\(";
		replace = ",";
		pattern = Pattern.compile(find);
		matcher = pattern.matcher(output);
		output = matcher.replaceAll(replace);

		return geometryPattern + output;
	}

	private String convertTableObjColumnList(Table table) {
		String insertStr = "(";
		for (int i = 0; i < table.getColumns().size(); i++) {
			insertStr = insertStr + table.getColumns().get(i).getName();

			if (i < table.getColumns().size() - 1) {
				insertStr = insertStr + ", ";
			}
		}
		return insertStr + ")";
	}

	public int processBlob(ResultSet rs, int i, String fileNamePrefix,
			int fisIndex, FileInputStream[] arrFis, PreparedStatement insertStmt)
			throws IOException, SQLException {
		BLOB blob = ((OracleResultSet) rs).getBLOB(i + 1);
		if (blob != null) {
			long blobLength = blob.length();
			this.lastMigratedDataSize += blobLength;

			InputStream bis = blob.getBinaryStream();

			int size = blob.getBufferSize();
			byte[] buffer = new byte[size];

			File blobFile = new File(Utility.getTempPath() + "mtk_blobdata"
					+ "_" + fileNamePrefix + fisIndex);
			FileOutputStream bos = new FileOutputStream(blobFile);
			int length = -1;
			while ((length = bis.read(buffer)) != -1) {
				bos.write(buffer, 0, length);
			}
			bis.close();
			bos.close();
			if (blobLength > 0L) {
				arrFis[fisIndex] = new FileInputStream(blobFile);
				insertStmt.setBinaryStream(i + 1, arrFis[(fisIndex++)],
						(int) blobFile.length());
			} else {
				insertStmt.setNull(i + 1, -2);
			}
		} else {
			insertStmt.setNull(i + 1, -2);
		}
		return fisIndex;
	}
}

/*

 */